package priv.xakml.fileassistant.entity;

import java.io.File;
import java.sql.*;

public class DbHelper {
    java.sql.Connection connection = null;
    final static String  index_db_path = "file-index.db";
    final static String  index_db_table_Dir = "Dir";
    final static String  index_db_table_files = "files";
    final static String  index_db_table_SubDir = "SubDir";
    final static String  index_db_table_photos = "photos";

    public DbHelper() throws SQLException, ClassNotFoundException {
        File db_file = new File(index_db_path);
        connection = getConnection(index_db_path);

        if(!this.tableExists(this.connection, index_db_table_Dir)){
            this.createDirTable(this.connection);
        }
        if(!this.tableExists(index_db_table_SubDir)){
            this.createSubDirTable(this.connection);
        }
        if(!this.tableExists(index_db_table_files)){
            this.createFileTable(this.connection);
        }
        if (!this.tableExists(index_db_table_photos)){
            this.createPhotosTable(this.connection);
        }
    }
    /**
     * 获取数据库连接
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
        return conn;
    }

    private Statement getStatement(Connection connection) throws SQLException, ClassNotFoundException {
        Statement statement = null;
        if (null == statement) statement = connection.createStatement();
        return statement;
    }
    /**
     * 数据库资源关闭和释放
     */
    public void dispose() {
        try {
            if (null != connection) {
                connection.close();
                connection = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //region 创建数据表
    private void createTable(String ddl_script,Connection  connection) throws SQLException, ClassNotFoundException {
        Statement statement = getStatement(connection);
        statement.execute(ddl_script);
        if (null != statement) {
            statement.close();
            statement = null;
        }

//        if (null != resultSet) {
//            resultSet.close();
//            resultSet = null;
//        }
    }


    private void createFileTable(Connection connection ) throws SQLException, ClassNotFoundException {
        String ddl = "CREATE TABLE [files] (\n" +
                "                                            [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
                "                                            [folder_id] INTEGER NOT NULL,\n" +
                "                                            [file_name] NVARCHAR(300) NOT NULL, \n" +
                "                                            [file_path] NVARCHAR(300) NOT NULL, \n" +
                "                                            [fileSize] int not null,\n" +
                "                                            [md5] char(32) not null,\n" +
                "                                            [sha1] char(40) not null," +
                "                                            [is_ignore] BOOLEAN DEFAULT false);" +
                "                                            CREATE INDEX [ix_md5] ON [files]([md5]);\n" +
                "                                            CREATE INDEX [ix_sha1] ON [files]([sha1]);";
        createTable(ddl,connection);
    }
    //region 判断表是否存在
    /**
     * 判断表是否存在
     * @param connection
     * @param table_name
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean tableExists(Connection connection,String table_name)throws SQLException, ClassNotFoundException {
        boolean exists = false;
        String sql = "SELECT COUNT(*) AS Counter from sqlite_master where type='table' and name = '"+table_name+"'";
        Statement statement = connection.createStatement();
        ResultSet set = statement.executeQuery(sql);
        if(set.next()){
            long count = set.getLong(1);
            if(count > 0)
                exists = true;
        }
        set.close();
        return exists;
    }

    /**
     * 判断指定的表名称是否已经存在
     * @param table_name 表名称
     * @return true:已存在； false 不存在，需要手动创建表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean tableExists(String table_name) throws SQLException, ClassNotFoundException {
        return this.tableExists(this.connection, table_name);
    }

    //endregion

    //region 新建表

    /**
     * 新建 photos 表
     * @param connection
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private void createPhotosTable(Connection connection) throws SQLException, ClassNotFoundException {
        String url_listTableScript = "CREATE TABLE [photos] (\n" +
                "                                            [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \n" +
                "                                            [subdir_id] INTEGER NOT NULL, \n" +
                "                                            [photo_path] NVARCHAR(50) NOT NULL, \n" +
                "                                            [fileSize] int not null,\n" +
                "                                            [shooting_time] datetime null,\n" +
                "                                            [md5] char(32) not null,\n" +
                "                                            [sha1] char(40) not null)";
        createTable(url_listTableScript,connection);
//        destroyed(connection);
    }

    /**
     * 新建photos表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private void createPhotosTable() throws SQLException, ClassNotFoundException {
        this.createPhotosTable(this.connection);
    }
    /**
     * 新建Dir表
     * @param connection
     */
    private void createDirTable(Connection connection) throws SQLException, ClassNotFoundException {
        String sql = "CREATE TABLE [Dir] (\n" +
                "                                                [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
                "                                                [dir_path] NVARCHAR(200) NOT NULL,\n" +
                "                                                [dir_path_hash] NVARCHAR(50) NULL,\n" +
                "                                                [files_count] int NOT NULL,\n" +
                "                                                [sub_dir_count] int NOT NULL);\n" +
                "                        CREATE UNIQUE INDEX [uni_dir_path_hash] ON [Dir]([dir_path_hash] ASC);";

        createTable(sql,connection);
//        destroyed(connection);
    }

    /**
     * 新建子目录
     * @param connection 已打开的数据库链接
     */
    private void createSubDirTable(Connection connection) throws SQLException, ClassNotFoundException {
        String sql = "CREATE TABLE [SubDir] (\n" +
                "                                                [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
                "                                                [homedir_id] int not null DEFAULT (-1),\n" +
                "                                                [subdir_path] NVARCHAR(200) NOT NULL,\n" +
                "                                                [subdir_path_hash] NVARCAR(50) NULL,\n" +
                "                                                [files_count] int NOT NULL,\n" +
                "                                                [photos_count] int NOT NULL);\n" +
                "                                                CREATE UNIQUE INDEX [uni_path_hash] ON [SubDir]([subdir_path_hash] ASC)";

        createTable(sql,connection);
    }

    //endregion


    /**
     * 保存索引的根目录
     * @param dir 目录对象
     * @return 是否保存成功
     * @throws SQLException
     */
    public boolean saveDir(Dir dir) throws SQLException {
        PreparedStatement pst = connection.prepareStatement("insert  into Dir(dir_path,dir_path_hash,files_count,sub_dir_count) values (?,?,?,?)" );
        pst.setString(1, dir.getDirPath());
        pst.setString(2, dir.getDirPathHash());
        pst.setInt(3, dir.getFilesCount());
        pst.setInt(4, dir.getSubDirCount());
        int isOK = pst.executeUpdate();
        if(isOK > 0) {
            ResultSet rs = pst.getGeneratedKeys();
            if (rs.next()) {
                int newId = rs.getInt(1);
                dir.setId(newId);
            }
        }
        return isOK > 0;
    }

    /**
     * 保存被索引的文件
     * @param f 文件类型
     * @return 是否保存成功
     * @throws SQLException
     */
    public boolean saveFile(file f) throws SQLException {
        PreparedStatement pst = connection.prepareStatement("insert  into files(folder_id,file_name,file_path,fileSize,md5,sha1) values (?,?,?,?,?,?)");
        pst.setInt(1, f.getFolder_id());
        pst.setString(2, f.getFile_name());
        pst.setString(3, f.getFile_path());
        pst.setLong(4, f.getFile_size());
        pst.setString(5, f.getMd5());
        pst.setString(6, f.getSha1());
        int isOK = pst.executeUpdate();
        return isOK > 0;
    }

    /**
     * 保存subdir数据
     * @param sub_dir subdir 对象
     * @return 是否保存成功
     * @throws SQLException
     */
    public boolean saveSubDir(SubDir sub_dir) throws SQLException {
        PreparedStatement preparedStatement = this.connection.prepareStatement("insert  into SubDir (subdir_path,subdir_path_hash,files_count,photos_count,homedir_id) values(?,?,?,?,?)");
        preparedStatement.setString(1, sub_dir.getSubDir_Path());
        preparedStatement.setString(2, sub_dir.getSubDir_Path_hash());
        preparedStatement.setInt(3, sub_dir.getFiles_count());
        preparedStatement.setInt(4, sub_dir.getPhotos_count());
        preparedStatement.setInt(5, sub_dir.getHomeDirId());
        int rows = preparedStatement.executeUpdate();
        if(rows > 0) {
            ResultSet rs = preparedStatement.getGeneratedKeys();
            if (rs.next()) {
                int newId = rs.getInt(1);
                sub_dir.setId(newId);
            }
        }
        return rows > 0;
    }

    /**
     * 批量保存子目录
     * @param subDirs 子目录
     */
    public void saveSubDir(SubDir[] subDirs) {
        int i = 0;
        int batch_size = 100;
        try {
            this.connection.setAutoCommit(false);
            PreparedStatement preparedStatement = this.connection.prepareStatement("insert  into SubDir (subdir_path,subdir_path_hash,files_count,photos_count) values(?,?,?,?)");
            for (SubDir dir : subDirs) {
                preparedStatement.setString(1, dir.getSubDir_Path());
                preparedStatement.setString(2, dir.getSubDir_Path_hash());
                preparedStatement.setInt(3, dir.getFiles_count());
                preparedStatement.setInt(4, dir.getPhotos_count());
                preparedStatement.addBatch();
                i++;
                if (i % batch_size == 0 || i == subDirs.length) {
                    preparedStatement.executeBatch(); // Execute every batch_size items.
                }
            }
            connection.commit();
        }catch (SQLException sql_ex){
            System.out.println("批量保存子目录失败：" +sql_ex.getMessage());
        }finally {
            try {
                this.connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //region 事务
    public void beginTransaction() throws SQLException {
        this.connection.setAutoCommit(false);
    }

    public void CommitTransaction() throws SQLException {
        this.connection.commit();
        this.connection.setAutoCommit(true);
    }

    public void rollbackTransaction() throws SQLException {
        this.connection.rollback();
    }
    //endregion
}
